import pandas as pd
import os
from tkinter import *
from tkinter.ttk import *
import datetime
import random
import numpy as np
import timeit
from openpyxl import load_workbook
def do_file(info):
    folder_path='D:/data'
    start_time=timeit.default_timer()
    for root, dirs, files in os.walk(folder_path):
        for filename in files:
            if '.xlsx' in filename:
                file_path = os.path.join(root, filename)
                print(filename)
                df = pd.read_excel(file_path)
                #读取最后一行数据
                last_row = df.iloc[len(df) - 1]
                #转成列表
                last_row_list = last_row.tolist()
                last_day=last_row_list[0]
                last_hour=last_row_list[1]
                add_num=last_row_list[2]#重要，需要增加的行数
                #准备好32组数据的标准差和平均值
                spec=[]
                for i in range(3,len(last_row_list)):
                    max_row=df.iloc[0].to_list()
                    max=max_row[i]
                    min_row=df.iloc[1].to_list()
                    min=min_row[i]
                    data=[]
                    for j in range(len(df) - 30,len(df)):
                        row=df.iloc[j].to_list()
                        data.append(row[i])
                    mean = sum(data) / len(data)
                    variance = sum((x - mean) ** 2 for x in data) / len(data)
                    std_dev = variance ** 0.5
                    spec.append([std_dev,mean,max,min])
                new_day=last_day
                new_hour=last_hour+datetime.timedelta(hours=4)
                if last_hour.hour==20:
                    new_day=last_day+datetime.timedelta(days=1)
                for ii in range(add_num):
                    new_row=[new_day,new_hour,ii+1]
                    for jj in range(0,len(spec)):
                        rand_value = round(np.random.normal(spec[jj][1], spec[jj][0]),3)
                        if rand_value>spec[jj][2]:
                            rand_value=round(spec[jj][2]-random.uniform(0.002,0.008),3)
                        elif rand_value<spec[jj][3]:
                            rand_value=round(spec[jj][3]+random.uniform(0.002,0.008),3)
                        new_row.append(rand_value)
                    df.loc[len(df)+ii] = new_row
                df.to_excel(file_path, index=False)
                wb=load_workbook(file_path)
                ws=wb.active
                for row in range(7,ws.max_row+1):
                    ws.cell(row,1).number_format = 'yyyy-mm-dd'
                    ws.cell(row,2).number_format = 'hh:mm'
                wb.save(file_path)
                wb.close()
    end_time=timeit.default_timer()
    final_str="Done!"+str(end_time-start_time)[:6]+"s"
    info.config(text=final_str)
app=Tk()
app.title("Add_Data")
app.geometry("250x100+500+200")
app.resizable(False, False)
app.configure(bg="white")
s=Style()
s.configure(".", background="white",font=('Arial', '20'))
info=Label(app, text="")
info.place(x=20, y=55)
btn=Button(app, text="Add", command=lambda:do_file(info))
btn.config(width=5)
btn.place(x=80, y=15)
app.mainloop()